package com.zretc.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.zretc.dao.SalesDao;
import com.zretc.entity.Product;
import com.zretc.entity.Sales;
import com.zretc.util.DBUtil;


/**
 * @author Administrator
 *商品销售的实现类
 */
public class SalesDaoImpl implements SalesDao{
	
	
	/**
	 * @return
	 * 查询销售列表
	 */
	
	@Override
	public List<Sales> find() {
		List<Sales> list = new ArrayList<>();
		ResultSet rs = DBUtil.doQuery("select sales_count,sales_price,sales_date,product_name from sales A inner join product B on A.product_id = B.product_id");
		try {
			while (rs.next()) {
				String salesDate = rs.getString("sales_date");
				Integer salesCount = rs.getInt("sales_count");
				float salesPrice = rs.getFloat("sales_price");
				String productName = rs.getString("product_name");
				list.add(new Sales(null, new Product(null,productName,null,null,null,null,null,null,null,null), salesCount, null, salesPrice, salesDate));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	

	/**
	 * @return
	 * 添加销售列表
	 */
	@Override
	public int insertSales(Sales sales) {
		String sql = "insert into sales(order_id, product_id, sales_count, sales_price, sales_date) values(?,?,?,?,?)";
		return DBUtil.doUpdate(sql, sales.getOrder().getOrderId(), sales.getProduct().getProductId(), sales.getSalesCount(), sales.getSalesPrice(), sales.getSalesDate());
	}
	
	
	/**
	 * @author wentao
	 * // 销售额排行前十商品（饼状图）
	 * @param sellerId
	 * @return
	 */
	@Override
	public List<Map<String, Object>> salesTopTenPie(Integer sellerId){
		String sql = "select p.product_name,sum(s.sales_price) as total from sales s inner join product p on p.product_id = s.product_id where p.seller_id=? group by p.product_id order by total desc limit 0,10";
		ResultSet rs = DBUtil.doQuery(sql, sellerId);
		List<Map<String, Object>> list = new ArrayList<>();
		try {
			while(rs.next()) {
				Map<String, Object> data = new HashMap<String, Object>();
				String productName = rs.getString(1);
				Float total = rs.getFloat(2);
				data.put("value", total);
				data.put("name", productName);
				list.add(data);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	};
	

	/**
	 * // 销售额排行前十商品（条形图）
	 * @author wentao
	 * @param sellerId
	 * @return
	 */
	@Override
	public Map<String,Object> salesNumberTopTenBar(Integer sellerId){
		String sql = "select p.product_name,sum(s.sales_count) as total from sales s inner join product p on p.product_id = s.product_id where p.seller_id=? group by p.product_id order by total desc limit 0,10";
		ResultSet rs = DBUtil.doQuery(sql, sellerId);
		List<String> productNameList = new ArrayList<String>();
		List<Integer> priceList = new ArrayList<Integer>();
		try {
			while(rs.next()) {
				String productName = rs.getString(1);
				Integer total = rs.getInt(2);
				productNameList.add(productName);
				priceList.add(total);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		Map<String, Object> data = new HashMap<String, Object>();
		data.put("productName", productNameList);
		data.put("price", priceList);
		return data;
	};
	
	

	/**
	 * // 销售量排行前十商品（饼状图）
	 * @author wentao
	 * @param sellerId
	 * @return
	 */
	@Override
	public Map<String, Object> salesTopTenBar(Integer sellerId){
		String sql = "select p.product_name,sum(s.sales_price) as total from sales s inner join product p on p.product_id = s.product_id where p.seller_id=? group by p.product_id order by total desc limit 0,10";
		ResultSet rs = DBUtil.doQuery(sql, sellerId);
		List<String> productNameList = new ArrayList<String>();
		List<Float> priceList = new ArrayList<Float>();
		try {
			while(rs.next()) {
				String productName = rs.getString(1);
				Float total = rs.getFloat(2);
				productNameList.add(productName);
				priceList.add(total);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		Map<String, Object> data = new HashMap<String, Object>();
		data.put("productName", productNameList);
		data.put("price", priceList);
		return data;
		
	};
	

	/**
	 * // 销售量排行前十商品（条形图）
	 * @author wentao
	 * @param sellerId
	 * @return
	 */
	@Override
	public List<Map<String,Object>> salesNumberTopTenPie(Integer sellerId){
		String sql = "select p.product_name,sum(s.sales_count) as total from sales s inner join product p on p.product_id = s.product_id where p.seller_id=? group by p.product_id order by total desc limit 0,10";
		ResultSet rs = DBUtil.doQuery(sql, sellerId);
		List<Map<String, Object>> list = new ArrayList<>();
		try {
			while(rs.next()) {
				Map<String, Object> data = new HashMap<String, Object>();
				String productName = rs.getString(1);
				Integer total = rs.getInt(2);
				data.put("value", total);
				data.put("name", productName);
				list.add(data);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	};
	

	/**
	 * // 根据时间查询销售额
	 * @author wentao
	 * @param sellerId
	 * @return
	 */
	@Override
	public Map<String,Object> salesListByDate(Integer sellerId,String beginTime ,String lastTime){
		String sql = "select date_format(s.sales_date,\"%Y-%m-%d\") as date,sum(s.sales_price) as total from sales s inner join product p on p.product_id = s.product_id where p.seller_id=? and s.sales_date between ? and ? group by date_format(s.sales_date,\"%Y-%m-%d\") ";
		ResultSet rs = DBUtil.doQuery(sql, sellerId,beginTime,lastTime);
		List<String> dateList = new ArrayList<String>();
		List<Float> moneyList = new ArrayList<Float>();
		try {
			while(rs.next()) {
				String date = rs.getString(1);
				Float total = rs.getFloat(2);
				 dateList.add(date);
				 moneyList.add(total);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		Map<String, Object> data = new HashMap<String, Object>();
		data.put("dateList", dateList);
		data.put("moneyList", moneyList);
		return data;
	}
	

	/**
	 * // 根据时间查询销量
	 * @author wentao
	 * @param sellerId
	 * @return
	 */
	@Override
	public Map<String,Object> salesNumberListByDate(Integer sellerId,String beginTime ,String lastTime){
		String sql = "select date_format(s.sales_date,\"%Y-%m-%d\") as date,sum(s.sales_count) as total from sales s inner join product p on p.product_id = s.product_id where p.seller_id=? and s.sales_date between ? and ? group by date_format(s.sales_date,\"%Y-%m-%d\") ";
		ResultSet rs = DBUtil.doQuery(sql, sellerId,beginTime,lastTime);
		List<String> dateList = new ArrayList<String>();
		List<Float> numberList = new ArrayList<Float>();
		try {
			while(rs.next()) {
				String date = rs.getString(1);
				Float total = rs.getFloat(2);
				dateList.add(date);
				numberList.add(total);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		Map<String, Object> data = new HashMap<String, Object>();
		data.put("dateList", dateList);
		data.put("numberList", numberList);
		return data;
	}
}
